/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sistemperpustakaan.model;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;
import javax.swing.JOptionPane;
import sistemperpustakaan.entity.Anggota;
import sistemperpustakaan.entity.TypeAnggota;
import sistemperpustakaan.entity.ViewAnggota;
import sistemperpustakaan.utility.DbUtility;

/**
 *
 * @author EKo
 */
public class AnggotaModel {

    Connection connection;
    List<ViewAnggota> listAnggota;
    Vector vectorTypeAnggota;
    ViewAnggota la;
    Anggota anggota;
    TypeAnggota typeAnggota;

    public AnggotaModel() {
        connection = new DbUtility().getConnection();
    }

    public Anggota getAnggota(int id) {
        String query = "SELECT * FROM anggota WHERE id=" + id;
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(query);
//            statement.setInt(1, id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                anggota = new Anggota(rs.getInt(1), rs.getString(2), rs.getInt(3),
                        rs.getString(4), rs.getInt(5), rs.getString(6), rs.getDate(7), rs.getString(8),
                        rs.getString(9),rs.getString(10));
            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data buku" + ex.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return anggota;
    }

    public Connection getConnection() {
        return connection;
    }

    public List<ViewAnggota> getListAnggota(int id, String nis_nik, String nama, String type) {
        listAnggota = new ArrayList<ViewAnggota>();
        String query = "CALL pCariAnggota (" + id + ", '" + nis_nik + "', '" + nama + "', '" + type + "')";
        
        CallableStatement statement = null;
        try {
            statement = connection.prepareCall(query);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                la = new ViewAnggota(
                        rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),
                        rs.getDate(7), rs.getString(8), rs.getString(9), rs.getString(10));
                listAnggota.add(la);

            }

        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data" + ex.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return listAnggota;
    }

    public Vector getVectorTypeAnggota() {
        vectorTypeAnggota = new Vector();
        String query = "SELECT tipe_anggota from tipe_anggota ORDER BY id";
        Statement statement = null;
        try {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            vectorTypeAnggota.add("");
            while (rs.next()) {
                vectorTypeAnggota.add(rs.getString(1));
            }
        } catch (SQLException e) {
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return vectorTypeAnggota;
    }

    public boolean insertAnggota(int id, String Nis, int idType, String nama, int jenisKelamin, String tempatLahir, String tglLahir, String alamat, String telepon, String email) {
        String query = "INSERT INTO anggota (id, nis_nik, tipe_anggota_id, nama, jenis_kelamin, kota_lahir, tanggal_lahir, "
                + "alamat, telepon, email) VALUES (fGenerateIdAnggota(),'" + Nis + "'," + idType + ",'" + nama + "','" + jenisKelamin + "','" + tempatLahir + "','" + tglLahir + "','" + alamat + "','" + telepon + "','" + email + "')";
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
//            for(int i=1; i<=10;i++)statement.setObject(i, a.getObject(i));
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }
//            System.out.println(query);
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when insert data \n Error: " + e.getMessage());
            try {
                connection.rollback();
                statement.close();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return true;
    }

    public boolean updateAnggota(int id, String Nis, int idType, String nama, int jenisKelamin,String tempatLahir, String tglLahir, String alamat, String telepon, String email, int id_lama) {

        String query = "UPDATE anggota  SET id=" + id + ",nis_nik='" + Nis + "', tipe_anggota_id=" + idType + ", nama='" + nama + "', jenis_kelamin='" + jenisKelamin + "', kota_lahir='" + tempatLahir + "', tanggal_lahir='" + tglLahir + "', "
                + "alamat='" + alamat + "', email='" + email + "', telepon='" + telepon + "' WHERE id=" + id_lama;
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
//            for(int i=1; i<=10;i++)statement.setObject(i, a.getObject(i));
//            statement.setInt(11, a.getId());

            if (statement.executeUpdate() == 1) {
                connection.commit();
            }
//            System.out.println(query);
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when update data \n Error: " + e.getMessage());
            try {
                connection.rollback();
                statement.close();
            } catch (Exception ex) {
            }
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return true;
    }

    public boolean deleteAnggota(int id) {
        String query = "DELETE FROM anggota WHERE id=?";
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
            statement.setInt(1, id);
            connection.setAutoCommit(false);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when delete data \n Error: " + e.getMessage());
            try {
                connection.rollback();
                statement.close();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return true;
    }

    public TypeAnggota getTypeAnggota(int id) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("SELECT * FROM tipe_anggota WHERE id=" + id);
            //statement.setInt(1, id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                typeAnggota = new TypeAnggota(rs.getInt(1), rs.getString(2));
            } else {
                typeAnggota = new TypeAnggota();
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data \n Error: " + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return typeAnggota;
    }

    public Anggota generateIdAnggota() {
        PreparedStatement statement = null;
        Anggota anggota = null;
        try {
            statement = connection.prepareStatement("SELECT fGenerateIdAnggota() AS Kode");
//            statement.setInt(1, idKategori);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
//                anggota = new Anggota(rs.getInt(1), "","","","","","","","");
                anggota = new Anggota(rs.getInt(1), null, 0, null, 0, null, null,null, null, null);
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data" + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return anggota;
    }
}
